﻿#include "SqliteDb.h"
#include <QVariant>
#include <QDebug>

SqliteDb SqliteDb::m_SqliteDb;
QString SqliteDb::m_dbName;
QSqlDatabase SqliteDb::m_sqlDb;
SqliteDb::SqliteDb()
{
	dbDir = QDir::currentPath();
}
SqliteDb::~SqliteDb()
{

}

bool SqliteDb::insertRow(const QString &tableName, const QMap<QVariant, QVariant> &dataMap)
{
    return addData(SqliteDb::m_dbName, tableName, dataMap);
}

bool SqliteDb::getRow(const QString &tableName, DB_TYPE_DATA &data, const DB_TYPE_DATA&filterMap)
{
    QString whereStr = getWhereStr(filterMap);
    return getData(SqliteDb::m_dbName, tableName, data, whereStr);
}

bool SqliteDb::getRows(const QString &tableName, DB_TYPE_LIST &data, const DB_TYPE_DATA &filterMap)
{
    QString whereStr = getWhereStr(filterMap);
    return getData(SqliteDb::m_dbName, tableName, data, whereStr);
}

bool SqliteDb::getRows(const QString &tableName, DB_TYPE_LIST &data, const QString &strWhere)
{
    return getData(SqliteDb::m_dbName, tableName, data, strWhere);
}

bool SqliteDb::delRow(const QString &tableName, const DB_TYPE_DATA&filterMap)
{
    QString whereStr = getWhereStr(filterMap);
    return delData(SqliteDb::m_dbName, tableName, whereStr);
}

bool SqliteDb::updateRow(const QString &tableName, const DB_TYPE_DATA &dataMap, const DB_TYPE_DATA &filterMap)
{
    QString whereStr = getWhereStr(filterMap);
    return updateData(SqliteDb::m_dbName, tableName, dataMap, whereStr);
}


//设置数据库存放路径
bool SqliteDb::setDbDir(QString dirPath)
{
	QDir dir(dirPath);
	if(dir.exists())
	{
		dbDir = dirPath;
		return true;
	}
	else
	{
		return false;
	}
}
//打开连接
bool SqliteDb::reOpenSql(const QString &dbName)
{
    SqliteDb::m_dbName = dbName;
	QString fileName = (dbDir + "/"+dbName + ".db");
	if(!QFile::exists(fileName))
	{
		return false;//数据库不存在
	}
	QFileInfo file(fileName);
	if(file.suffix() != "db")
		return false;
    m_sqlDb = QSqlDatabase::database(dbName);
    if(!m_sqlDb.isValid())
	{
        m_sqlDb = QSqlDatabase::addDatabase("QSQLITE",dbName);
        m_sqlDb.setDatabaseName(fileName);
        if (!m_sqlDb.open())
		{
			return false;//打开失败
		}
	}
	dbFilePath = fileName;
	databaseName = dbName;//数据库名字
    return true;
}

int SqliteDb::getMaxId(const QString &tableName)
{
    QString strSql = QString("select max(id) from %1;").arg(tableName);
    DB_TYPE_LIST dataMap;
    queryExec(strSql, dataMap);
    if(dataMap.isEmpty())
    {
        return 0;
    }
    return dataMap[0]["max(id)"].toInt();
}

QSqlDatabase SqliteDb::getOpenSql(QString dbName)
{
	QString fileName = (dbDir + "/"+dbName + ".db");
	if(!QFile::exists(fileName))
	{
		return QSqlDatabase();//数据库不存在
	}
	QFileInfo file(fileName);
	if(file.suffix() != "db")
		return QSqlDatabase();
    m_sqlDb = QSqlDatabase::database(dbName);
    if(!m_sqlDb.isValid())
	{
        m_sqlDb = QSqlDatabase::addDatabase("QSQLITE",dbName);
        m_sqlDb.setDatabaseName(fileName);
        if (!m_sqlDb.open())
		{
			return QSqlDatabase();//打开失败
		}
	}
    return m_sqlDb;
}

//关闭连接
bool SqliteDb::closeSql()
{
	if(databaseName.isEmpty())
		return true;
	if(!QFile::exists(dbFilePath))
	{
		return false;//数据库不存在
	}
    m_sqlDb = QSqlDatabase::database(databaseName);
    if(!m_sqlDb.isValid())
	{
		return true;
	}
    m_sqlDb.close();
    m_sqlDb = QSqlDatabase::database();
	QSqlDatabase::removeDatabase(databaseName);
	databaseName = "";
	dbFilePath = "";
	return true;
}
//生成一个db文件
bool SqliteDb::creatDbFile(QString dbName)
{
	QString fileName = (dbDir + "/"+dbName + ".db");
	QFile file(fileName);
	if(file.open(QIODevice::WriteOnly))
	{
		file.close();
	}
	else
	{
		return false;
	}
	return true;
}
//错误打印
void SqliteDb::errorSql(QString sql)
{
	errorSqlText = sql;
    //QString("数据库执行错误：%1 ")+sql.toUtf8().constData();
}

QString SqliteDb::getWhereStr(const DB_TYPE_DATA &filterMap)
{
    QString whereStr = " where ";
    for(auto pIter=filterMap.begin(); pIter!=filterMap.end(); pIter++)
    {
        if(pIter->type() == QVariant::String)
        {
            whereStr += (QString("%1 = '%2' and").arg(pIter.key().toString()).arg(pIter.value().toString()));
        }
        else
        {
            whereStr += (QString("%1 = %2 and").arg(pIter.key().toString()).arg(pIter.value().toString()));
        }
    }

    if(whereStr.endsWith("and"))
    {
       whereStr = whereStr.remove(whereStr.size()-3, 3);
       whereStr += ";";
    }
    else if(filterMap.isEmpty())
    {
        whereStr.clear();
    }
    return whereStr;
}
//获取错误的数据库语句
QString SqliteDb::getErrorSql()
{
	if(databaseName.isEmpty())
	{
		return "db  not setting";//数据库未设置
	}
	return errorSqlText;
}



//执行sql语句，不获取结果
bool SqliteDb::queryExec(QString dbName,QString queryStr)
{
	if(databaseName.isEmpty())
	{
		if(!reOpenSql(dbName))
		{
			return false;
		}
	}
	QSqlQuery query(QSqlDatabase::database(dbName, true));
	if(!query.exec(queryStr))
	{
		errorSql(queryStr);
		return false;
	}
	return  true;
}
//执行sql语句，并获取结果
bool SqliteDb::queryExec(QString dbName,QString queryStr,QList<QMap<QVariant,QVariant>> &data)
{
	data.clear();
	if(databaseName.isEmpty())
	{
		if(!reOpenSql(dbName))
		{
			return false;
		}
	}
	QSqlQuery query(QSqlDatabase::database(dbName, true));
	if(!query.exec(queryStr))
	{
		errorSql(queryStr);
		return false;
	}
	QSqlRecord rec = query.record();
	while(query.next())
	{
        QMap<QVariant,QVariant> rowData;
		for(int i =0;i<rec.count();i++)
		{
			QVariant::Type ty =  query.value(i).type();
			if( QVariant::Type::Date == ty)
			{
				QDate temp = query.value(i).toDate();
				rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd");
			}
			else if( QVariant::Type::Time == ty)
			{
				QTime temp = query.value(i).toTime();
				rowData[rec.fieldName(i)]=temp.toString("hh:mm:ss");
			}
			else if( QVariant::Type::DateTime == ty)
			{
				QDateTime temp = query.value(i).toDateTime();
				rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd hh:mm:ss");
			}
			else
                rowData[rec.fieldName(i)]=query.value(i);
		}
		data.append(rowData);
	}
	return  true;
}
//获取数据
bool SqliteDb::getData(QString dbName,QString tableName,QMap<QVariant,QVariant> &data,QString sqlWhere)
{
	data.clear();
    DB_TYPE_LIST dataList;
	if(!getData(dbName,tableName,dataList,sqlWhere))
	{
		return false;
	}
	if(dataList.count() > 0)
	{
		data = dataList[0];
	}
	return true;
}
//获取数据
bool SqliteDb::getData(QString dbName,QString tableName,QList<QMap<QVariant,QVariant>> &data,QString sqlWhere)
{
	QString queryStr="select * from "+tableName;
	if(!sqlWhere.isEmpty())
		queryStr+=" "+sqlWhere;
	if(!queryExec(dbName,queryStr,data))
	{
		return  false;
	}
	return  true;
}
//获取数据
bool SqliteDb::getData(QString dbName,QString tableName,QMap<QVariant,QVariant> columndata,QList<QMap<QVariant,QVariant>> &data,QString sqlWhere)
{
	QString colunmStr;
	if(columndata.count() == 0)
		colunmStr = "*";
	else
	{
        QVariantList keys = columndata.keys();
		foreach(auto key,keys)
		{
            QString column = QString("%1 AS `%2`").arg(key.toString()).arg(columndata[key].toString());
			if(!colunmStr.isEmpty())
				colunmStr += ",";
			colunmStr += column;
		}
	}
	QString queryStr = QString("SELECT %1 FROM %2 %3").arg(colunmStr).arg(tableName).arg( sqlWhere);
	if(!queryExec(dbName,queryStr,data))
	{
		return  false;
	}
	return  true;
}
//增加
bool SqliteDb::addData(QString dbName,QString tableName,const QMap<QVariant,QVariant> &data)
{
	if(data.isEmpty())
		return false;
	QString queryStr="insert into "+tableName+" ";
	QString fieldStr="(",valueStr="values(";
    QMap<QVariant,QVariant>::iterator it;
    for (auto it = data.begin(); it != data.end(); ++it )
	{
        fieldStr+=it.key().toString()+",";
        valueStr+="'"+it.value().toString()+"',";
	}
	fieldStr=fieldStr.left(fieldStr.length()-1);
	valueStr=valueStr.left(valueStr.length()-1);
	fieldStr+=")";
	valueStr+=")";
	queryStr+=fieldStr+" "+valueStr;
    qDebug() << queryStr;
	if(!queryExec(dbName,queryStr))
	{
		return false;
	}
	return true;
}
//删除
bool SqliteDb::delData(QString dbName, QString tableName, QString sqlWhere)
{
	QString queryStr="delete from "+tableName;
	if(!sqlWhere.isEmpty())
		queryStr+=" "+sqlWhere;
	if(!queryExec(dbName,queryStr))
	{
		return false;
	}
	return true;
}
//修改
bool SqliteDb::updateData(QString dbName,QString tableName,QMap<QVariant,QVariant> data,QString sqlWhere)
{
    QString queryStr="update "+tableName+" ";
	QString setStr="set ";
    for (auto it = data.begin(); it != data.end(); ++it )
	{
        setStr+=it.key().toString()+"='"+it.value().toString()+"'";
		setStr+=",";
	}
	setStr=setStr.left(setStr.length()-1);
	queryStr+=setStr;
	if(!sqlWhere.isEmpty())
		queryStr+=" "+sqlWhere;
	if(!queryExec(dbName,queryStr))
	{
		return false;
	}
	return true;
}
bool SqliteDb::transaction()
{
	if(databaseName.isEmpty())
		return false;
    return  m_sqlDb.transaction();
}
bool SqliteDb::commit()
{
	if(databaseName.isEmpty())
		return false;
    return  m_sqlDb.commit();
}
//执行sql语句，不获取结果
bool SqliteDb::queryExec(QString sqlStr)
{
	if(databaseName.isEmpty())
		return false;
	return  queryExec(databaseName,sqlStr);
}
//执行sql语句，并获取结果
bool SqliteDb::queryExec(QString sqlStr,QList<QMap<QVariant,QVariant>> &data)
{
	if(databaseName.isEmpty())
		return false;
	return  queryExec(databaseName,sqlStr,data);
}
//获取数据
bool SqliteDb::getData(QString tableName,QMap<QVariant,QVariant> &data,QString sqlWhere)
{
	if(databaseName.isEmpty())
		return false;
	return  getData(databaseName,tableName,data,sqlWhere);
}
//获取数据
bool SqliteDb::getData(QString table,QList<QMap<QVariant,QVariant>> &data,QString sqlWhere)
{
	if(databaseName.isEmpty())
		return false;
	return  getData(databaseName,table,data,sqlWhere);
}
//获取数据
bool SqliteDb::getData(QString tableName,QMap<QVariant,QVariant> columndata,QList<QMap<QVariant,QVariant>> &data,QString sqlWhere)
{
	if(databaseName.isEmpty())
		return false;
	return  getData(databaseName,tableName,columndata,data,sqlWhere);
}

//删除
bool SqliteDb::delData(QString tableName,QString sqlWhere)
{
	if(databaseName.isEmpty())
		return false;
	return  delData(databaseName,tableName,sqlWhere);
}
//修改
bool SqliteDb::updateData(QString tableName,QMap<QVariant,QVariant> data,QString sqlWhere)
{
	if(databaseName.isEmpty())
		return false;
	return  updateData(databaseName,tableName,data,sqlWhere);
}
//

